1. Field of the Invention
This invention relates in general to database management systems performed by computers, and in particular, to the incremental maintenance of summary tables with complex grouping expressions.
2. Description of Related Art
Computer systems incorporating a Relational Database Management System (RDBMS) using a Structured Query Language (SQL) interface are well known in the art. The SQL interface has evolved into a standard language for performing operations in an RDBMS and has been adopted as such by both the American Nationals Standard Organization (ANSI) and the International Standards Organization (ISO).
One application for an RDBMS is a data warehouse. The star schema of a typical data warehouse is comprised of a small number of very large fact tables and some number of relatively small dimension tables. The complex analytical queries that are evaluated in data warehouse environments often join one or more of the fact tables together with some dimension tables to pick up dimension attributes and then aggregate the results of these joins. A common characteristic of the data warehouse is that data from operational systems are periodically propagated to the warehouse. For example, bulk load append operations can be applied to the fact tables on a nightly basis. Old data can also be periodically purged from the fact tables via delete statements. Dimension tables are typically more static in nature and are updated less frequently.
The high volume of data in the warehouse requires that the number of fact table scans must be minimized. Materialized views (or automatic summary tables as they are known in DB2(r) UDB(tm)) are commonly used in data warehouses to help achieve this goal. Aggregated result sets that can be used in the computation of multiple queries are pre-computed and materialized in tables. Using complex grouping expressions for the definition of such materialized views imply that a single table can hold values aggregated according to multiple grouping combinations. The queries are then rerouted (either manually by rewriting the query or automatically by sophisticated query compilers) to refer to the materialized views rather than the base fact table data.
Materialized views with complex grouping expressions must be maintained efficiently to reflect the periodic updates to the data warehouse. Given the vast amount of data involved, there is a need in the art for efficient maintenance methods for materialized views with complex grouping expression. Moreover, there is a need in the art for maintenance methods that are incremental in nature to avoid full re-computation of the queries of the materialized views.
To overcome the limitations in the prior art described above, and to overcome other limitations that will become apparent upon reading and understanding the present specification, the present invention discloses a method, apparatus, and article of manufacture for incrementally maintaining a materialized view with complex grouping expressions derived from at least one base table in a database stored on a computer. In accordance with the method, subsequent to performing a modification to at least one base table in a transaction, a data stream is built comprising modifications to be propagated to the materialized view.